package com.edu.test.excel;

import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @作者 five-five
 * @创建时间 2020/12/11
 */
public class ExcelUtil {
    public static List<Map<String, Object>> readXlsx(List<String> list, String s) {
        return readXlsx(list, s, 0);
    }

    /**
     * 转换器接口
     */
    interface TypeConverter<T, K> {
        /**
         * 转换器方法
         *
         * @param type 指定转换的类型
         * @param k    进行转换的类型
         * @return 最后返回的是type这个类型的
         */
        T getResult(Class<T> type, K k);
    }

    private static final String LONG_TYPE = "java.lang.Long";
    private static final String DATE_TYPE = "java.util.Date";
    private static final String BOOLEAN_TYPE = "java.lang.Boolean";
    private static final String INTEGER_TYPE = "java.lang.Integer";
    private static final String DOUBLE_TYPE = "java.lang.Double";
    private static final String STRING_TYPE = "java.lang.String";
    private static List<TypeConverter> typeConverters = new ArrayList<>();
    private static List<Field> fields = new ArrayList<>();

    /**
     * @param fields
     * @param filePath excel文件路径
     * @param index 哪里开始
     * @return
     */
    public static List<Map<String, Object>> readXlsx(List<String> fields, String filePath,int index) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        File file = new File(filePath);
        //单线程跑
        try {
            XSSFWorkbook sheets = new XSSFWorkbook(new FileInputStream(file));
            for (XSSFSheet sheet : sheets) {
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int i = index; i < physicalNumberOfRows; i++) {
                    //每一行的数据储存在map对象
                    Map<String, Object> map = new HashMap<String, Object>();
                    //迭代行
                    XSSFRow row = sheet.getRow(i);
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();//拿每一行的列
                    if (fields.size() < physicalNumberOfCells) {
                        System.err.println("你给出的列名有误");
                        return list;
                    }
                    for (int j = 0; j < physicalNumberOfCells; j++) {
                        XSSFCell cell = row.getCell(j);
                        if (cell!=null) {
                            map.put(fields.get(j), getValueByType(cell));
                        }else {
                            map.put(fields.get(j), null);
                        }
                    }
                    list.add(map);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 为对象赋值
     *
     * @param list
     * @param tClass
     * @param <T>
     */
    public static <T> List<T> valuationPOJO(List<Map<String, Object>> list, Class<T> tClass) {
        List<T> pojos = new ArrayList<>(list.size());
        try {
            Constructor<T> constructor = tClass.getConstructor();
            if (list != null && list.size() != 0) {
                for (Map<String, Object> map : list) {
                    T t = constructor.newInstance();
                    //拿属性
                    List<Field> allFields = getAllFields(tClass);
                    for (Field field : allFields) {
                        field.setAccessible(true);
                        if (!map.containsKey(field.getName())) {
                            System.err.println("兄弟，你的属性列没给对");
                            return pojos;
                        }
                        field.set(t, transType(field.getType(), map.get(field.getName())));
                    }
                    pojos.add(t);
                }
            } else {
                //你这集合是没有值的
                return pojos;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pojos;
    }

    public static boolean addTypeHandler(TypeConverter typeConverter) {
        return typeConverters.add(typeConverter);
    }

    /**
     * 按照类型进行强转
     *
     * @param type
     * @param o
     * @return
     */
    private static Object transType(Class<?> type, Object o) {
        String string = o.toString();
        //根据传值进行接口实现类的选择
        if (type.getName().equals(DOUBLE_TYPE)) {
            return Double.valueOf(string);
        }
        if (type.getName().equals(INTEGER_TYPE)) {
            return Integer.valueOf(Double.valueOf(string).intValue());
        }
        if (type.getName().equals(BOOLEAN_TYPE)) {
            return Boolean.valueOf(string.substring(0,1));
        }
        if (type.getName().equals(DATE_TYPE)) {
            return new Date(string);
        }
        if (type.getName().equals(LONG_TYPE)) {
            return Long.valueOf(Double.valueOf(string).longValue());
        }
        if (type.getName().equals(STRING_TYPE)) {
            return string;
        }
        return type.cast(o.toString());
    }

    /**
     * 使用递归把对象及其父类的属性都拿到
     *
     * @param t
     */
    private static List<Field> getAllFields(Class t) {
        //先把自己的属性打印出来
        Field[] declaredFields = t.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            fields.add(declaredField);
        }
        Class<?> superclass = t.getSuperclass();
        if (superclass == null) {
            return fields;
        }
        return getAllFields(superclass);
    }

    /**
     * 根据类型来获取列的值
     *
     * @param cell
     * @return
     */
    private static Object getValueByType(XSSFCell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                return "";
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_ERROR:
                return ErrorEval.getText(cell.getErrorCellValue());
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
//                    DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
//                    return sdf.format(Cell.getDateCellValue());
                    return cell.getDateCellValue();
                }
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "你这个type不对" + cell.getCellType();
        }
    }
}
